/**
 * 
 */
package com.dingding.dao.sql;

/**
 * @Title: UserdetailServiceSQL
 * @Description: 用户详情表和车主信息表联表sql
 * @author Li Yi
 * @date 2015-10-26 下午05:56:24
 */

public class UserdetailServiceSQL {

	// 查看对方用户信息
	public static final String GET_USER_DETAIL_INFO = "SELECT  u.nickName,u.age,u.occupation,u.origin,u.userAvatar,u.userTabloidAvatar,v.lisencePlate,v.vehicleType,v.vehicleModel,v.vehicleColor,v.vehicleImage FROM userdetail u LEFT JOIN vehicleinfo v ON u.loginId = v.loginId where u.loginId = ?";

	// 设置用户头像
	public static final String SET_USER_AVATAR = "UPDATE userdetail ud SET ud.userAvatar = ? WHERE ud.loginId = ?";

	// 获取用户详细信息
	public static final String GET_USER_DETAIL = "from userdetail ud where ud.loginId = ?";

	// 根据所在城市获取所有车主用户id
	public static String getIdsByCurrentCity(Integer orderType) {

		StringBuffer str = new StringBuffer(
				"SELECT ul.aliasId, CASE WHEN ul.loginEquipment = 1 THEN 'iOS' ELSE 'Android' END deviceType FROM userdetail ud INNER JOIN userlogin ul ON ud.loginId = ul.uid WHERE ud.currentCity LIKE ? AND ud.loginId != ? AND ul.userStatus = 1");

		// 乘客下单只查询车主
		// 车主下单查询全部
		if (orderType == 1) {
			str.append(" AND ul.userRole = 1");
		}

		return str.toString();
	}

	// 根据订单信息获取推送用户Id
	public static final String GET_PUSH_USER_LIST = "SELECT ul.aliasId,CASE ul.loginEquipment WHEN 1 THEN 'iOS' ELSE 'Android' END loginEquipment,ud.startworkTime,ud.endworkTime FROM userordert u INNER JOIN userdetail ud ON u.placeOrderUserId != ud.loginId INNER JOIN userlogin ul ON ul.uid = ud.loginId WHERE u.orderStatus = 1 AND ul.userStatus = 1 AND ((ROUND(6378.138*2*ASIN(SQRT(POW(SIN( (u.startLatitude * PI()/180- ud.homeLatitude * PI()/180)/2),2)+COS( u.startLatitude * PI()/180)*COS( ud.homeLatitude * PI()/180)* POW(SIN( ( u.startLongitude * PI()/180- ud.homeLongitude * PI()/180)/2),2)))) < ? AND ROUND(6378.138*2*ASIN(SQRT(POW(SIN( (u.endLatitude * PI()/180- ud.companyLatitude * PI()/180)/2),2)+COS( u.endLatitude * PI()/180)*COS( ud.companyLatitude * PI()/180)* POW(SIN( ( u.endLongitude * PI()/180- ud.companyLongitude * PI()/180)/2),2)))) < ? ) OR (ROUND(6378.138*2*ASIN(SQRT(POW(SIN( (u.startLatitude * PI()/180- ud.companyLatitude * PI()/180)/2),2)+COS( u.startLatitude * PI()/180)*COS( ud.companyLatitude * PI()/180)* POW(SIN( ( u.startLongitude * PI()/180- ud.companyLongitude * PI()/180)/2),2)))) < ?  AND ROUND(6378.138*2*ASIN(SQRT(POW(SIN( (u.endLatitude * PI()/180- ud.homeLatitude * PI()/180)/2),2)+COS( u.endLatitude * PI()/180)*COS( ud.homeLatitude * PI()/180)* POW(SIN( ( u.endLongitude * PI()/180- ud.homeLongitude * PI()/180)/2),2)))) < ? )) AND u.oid = ?";

	public static final String GET_PUSH_USERINFO = "SELECT ud.userAvatar,ud.nickName,ud.tellphone,vi.lisencePlate,vi.vehicleType,vi.vehicleModel,vi.vehicleColor,vi.vehicleImage,ud.userTabloidAvatar FROM userdetail ud LEFT JOIN vehicleinfo vi ON vi.loginId = ud.loginId WHERE ud.loginId = ?";

	/**
	 * 车主评价完成后更新车主信誉等级
	 * @author CHEHUANBO 
	 */
	public static final String UPDATE_REPUTE_LEVEL = "UPDATE userdetail ud SET ud.userLevel = (SELECT FORMAT(AVG(oc.evaluateLevel),1) FROM userorder uo INNER JOIN ordercomment oc ON oc.orderId = uo.oid WHERE (uo.placeOrderUserId = ? AND uo.orderType = 1) OR (uo.grabOrderUserId = ? AND uo.orderType = 2) AND oc.evaluateLevel IS NOT NULL) WHERE ud.loginId = ?";
}
